package com.pur.zsms.supplier.web;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import net.sf.json.JSONObject;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import com.pur.zsms.common.exception.GenericException;
import com.pur.zsms.common.support.PageController;
import com.pur.zsms.common.support.PaginationSupport;
import com.pur.zsms.common.util.ExcelDropDownUtil;
import com.pur.zsms.common.util.FileUtill;
import com.pur.zsms.lockmanage.service.ILockTransService;
import com.pur.zsms.matermanage.mater.po.MaterPo;
import com.pur.zsms.matermanage.mater.service.IMaterService;
import com.pur.zsms.matermanage.mater.vo.MaterVo;
import com.pur.zsms.pmsnmanage.user.po.UserPo;
import com.pur.zsms.pmsnmanage.user.service.IUserService;
import com.pur.zsms.po.DistributorSupplierMaterPo;
import com.pur.zsms.supplier.dao.SupplierDao;
import com.pur.zsms.supplier.po.SupplierDistributeMaterPo;
import com.pur.zsms.supplier.po.SupplierPo;
import com.pur.zsms.supplier.service.ISupplierDistributeService;
import com.pur.zsms.supplier.service.ISupplierService;
import com.pur.zsms.supplier.vo.SupplierDistributeMateVo;
import com.pur.zsms.systemsetting.datadictionary.service.IDataDictionaryService;
import com.pur.zsms.timecontrol.service.ITimeControlService;
import com.pur.zsms.vo.DistributorSupplierMaterVo;

/**
 * 供应商分货
 * 
 * @author yuxin
 * 
 */
@Controller
@RequestMapping(value = "/supplierDistribute")
public class SupplierDistributeController {
	@SuppressWarnings("unused")
	private static final Logger log = LoggerFactory.getLogger(SupplierDistributeController.class);
	protected int page = 1;

	protected int pageSize = 15;

	PaginationSupport pageList;

	public int getPage() {
		return page;
	}

	public void setPage(int page) {
		this.page = page;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	@Resource(name = "supplierDao")
	private SupplierDao supplierDao;

	@Resource(name = "dictionaryService")
	private IDataDictionaryService dataDictionaryService;

	@Resource(name = "materService")
	private IMaterService materService;

	@Resource(name = "supplierService")
	private ISupplierService supplierService;

	@Resource(name = "supplierDistributeService")
	private ISupplierDistributeService supplierDistributeService;

	@Resource(name = "timeControlService")
	private ITimeControlService timeControlService;

	@Resource(name = "userService")
	private IUserService userService;

	@Resource(name = "lockTransService")
	private ILockTransService lockTransService;

	// 手机分菜页面
	@RequestMapping(value = "/findDistributeMater.do")
	@ResponseBody
	public String findDistributeMater(@RequestParam(required = false) String page, @RequestParam(required = false) String rows,
			SupplierDistributeMateVo supplierDistributeMateVo, HttpSession session) throws GenericException {
		JSONObject json = new JSONObject();

		UserPo userInfo = (UserPo) session.getAttribute("user");
		int supId = supplierService.getSupIdByUserId(userInfo.getUserId());
		supplierDistributeMateVo.setSupId(supId);

		if (supplierDistributeMateVo.getInitFlag() == 0) {
			json.put("success", false);
			json.put("total", 0);
			return json.toString();
		}

		int size = supplierDistributeService.findDistributeMater(supplierDistributeMateVo);
		page = "1";
		rows = String.valueOf(size);
		PageController pageController = new PageController(Integer.parseInt(page), Integer.parseInt(rows));
		this.pageList = supplierDistributeService.findDistributeMater(pageController, supplierDistributeMateVo);
		List<SupplierDistributeMateVo> list = new ArrayList<SupplierDistributeMateVo>();
		List<SupplierDistributeMateVo> footerlist = new ArrayList<SupplierDistributeMateVo>();

		List items = pageList.getItems();

		BigDecimal ordMaterWeightAll = new BigDecimal(0);
		if (items != null) {
			for (int i = 0; i < items.size(); i++) {
				Object[] item = (Object[]) items.get(i);
				SupplierDistributeMateVo f = new SupplierDistributeMateVo();
				f.setShopId((Integer) item[0]);
				f.setShopName((String) item[1]);
				f.setMaterId((Integer) item[2]);
				f.setOrdMaterWeight((BigDecimal) item[3]);
				f.setLeaveMessage((String) item[4]);
				f.setMaterName((String) item[5]);
				f.setUnit((String) item[6]);
				f.setSpecifications((String) item[7]);
				f.setBrands((String) item[8]);
				// 总计
				ordMaterWeightAll = ordMaterWeightAll.add((BigDecimal) item[3]);
				list.add(f);
			}
		}
		SupplierDistributeMateVo footer = new SupplierDistributeMateVo();
		footer.setMaterName("数量合计");
		footer.setOrdMaterWeight(ordMaterWeightAll);
		footerlist.add(footer);

		json.put("success", true);
		json.put("total", pageList.getTotalCount());
		json.put("rows", list);
		json.put("footer", footerlist);

		return json.toString();
	}

	@RequestMapping(value = "/findMaterId.do")
	@ResponseBody
	public String findMaterId(@RequestParam(required = false) String page, @RequestParam(required = false) String rows, MaterVo materVo,
			HttpSession session) throws GenericException {
		JSONObject json = new JSONObject();

		UserPo userInfo = (UserPo) session.getAttribute("user");
		int supId = supplierService.getSupIdByUserId(userInfo.getUserId());
		materVo.setSupId(supId);

		if (materVo.getInitFlag() == 0) {
			json.put("success", false);
			json.put("total", 0);
			return json.toString();
		}

		if (page == null || page.equals(""))
			page = "1";
		if (rows == null || rows.equals(""))
			rows = "10";
		PageController pageController = new PageController(Integer.parseInt(page), Integer.parseInt(rows));
		this.pageList = supplierDistributeService.findMaterId(pageController, materVo);
		List<MaterVo> list = new ArrayList<MaterVo>();

		List items = pageList.getItems();

		BigDecimal ordMaterWeightAll = new BigDecimal(0);
		if (items != null) {
			for (int i = 0; i < items.size(); i++) {
				Object[] item = (Object[]) items.get(i);
				MaterVo f = new MaterVo();
				f.setMaterId((Integer) item[0]);
				f.setMaterName((String) item[1]);
				f.setMaterType((Integer) item[2]);
				f.setBrands((String) item[3]);
				f.setSpecifications((String) item[4]);
				f.setUnit((String) item[5]);

				list.add(f);
			}
		}
		json.put("success", true);
		json.put("total", pageList.getTotalCount());
		json.put("rows", list);

		return json.toString();
	}

	/**
	 * 生成分货表
	 * 
	 */
	@RequestMapping(value = "/importSupDistribute.do", method = { RequestMethod.POST })
	@ResponseBody
	public String importSupDistribute(HttpServletRequest request, HttpServletResponse response, HttpSession session1, String orderDate, int supId)
			throws Exception {

		JSONObject json = new JSONObject();
		HttpSession session = request.getSession();
		session.setAttribute("state", null);
		// 生成提示信息，
		String codedFileName = null;
		OutputStream fOut = null;
		UserPo userInfo = (UserPo) session1.getAttribute("user");

		/*
		 * // 增加对时间的校验 boolean flagTime = false; try { flagTime =
		 * timeControlService.timeControl(userInfo.getUserId(), orderDate); }
		 * catch (Exception e2) { e2.printStackTrace(); } if (!flagTime && supId
		 * == 0) { json.put("msg", "msg001");
		 * 
		 * json.put("success", false); return json.toString(); }
		 */

		try {

			// 根据用户id得出supid

			if (supId == 0) {
				supId = supplierService.getSupIdByUserId(userInfo.getUserId());
			}

			boolean lockFlag = lockTransService.lockTrans("importSupDistribute.do", supId);
			if (!lockFlag) {
				json.put("msg", "locked");
				json.put("success", false);
				return json.toString();
			}
			// 进行转码，使其支持中文文件名
			// codedFileName = java.net.URLEncoder.encode("Quotation" + supId +
			// "_" + orderDate, "UTF-8");
			// response.addHeader("Content-Disposition",
			// "attachment;   filename=" + codedFileName + ".xls");
			// 产生工作簿对象
			HSSFWorkbook workbook = new HSSFWorkbook();
			// 产生工作表对象

			List shopIdList = supplierService.getUserIdDistributePoList(orderDate, supId);

			for (Object object : shopIdList) {

				String shopIdAll = object.toString();

				HSSFSheet sheet = workbook.createSheet();
				// 打印格式
				sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3);// 页边距（上）
				sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3);// 页边距（下）
				sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.3);// 页边距（左）
				sheet.setMargin(HSSFSheet.RightMargin, (double) 0.3);// 页边距（右
				sheet.setHorizontallyCenter(true);// 打印水平居中

				// 设定自动适应列宽
				for (int i = 0; i < 20; i++) {
					sheet.autoSizeColumn(i);
				}
				// 边框
				HSSFCellStyle setBorder = workbook.createCellStyle();
				setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
				setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
				setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
				setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
				setBorder.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
				// sheet.setColumnHidden(0, true);// 隐藏列
				sheet.setColumnHidden(1, true);// 隐藏列
				sheet.setColumnHidden(2, true);// 隐藏列
				sheet.setColumnHidden(3, true);// 隐藏列
				sheet.setColumnHidden(4, true);// 隐藏列
				sheet.setColumnHidden(5, true);// 隐藏列

				sheet.setColumnHidden(6, true);// 隐藏列
				sheet.setColumnHidden(8, true);// 隐藏列
				sheet.setColumnHidden(12, true);// 隐藏列 总重量

				sheet.setColumnWidth(0, 2000);// 宽度设置
				sheet.setColumnWidth(7, 2000);// 宽度设置
				sheet.setColumnWidth(11, 2000);// 宽度设置

				int sheetIndex = 0;
				// 初始行设置
				HSSFRow rowfist = sheet.createRow((int) 0);// 创建一行
				rowfist.setHeight((short) 340);// 行高

				int totalLine = 0;

				HSSFCell cellFirst1 = rowfist.createCell(totalLine);// 创建一列
				cellFirst1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst1.setCellValue("材料Id");
				cellFirst1.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst3 = rowfist.createCell(totalLine);// 创建一列
				cellFirst3.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst3.setCellValue("材料品级");
				cellFirst3.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst4 = rowfist.createCell(totalLine);// 创建一列
				cellFirst4.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst4.setCellValue("供货商ID");
				cellFirst4.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst5 = rowfist.createCell(totalLine);// 创建一列
				cellFirst5.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst5.setCellValue("供货商名");
				cellFirst5.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst7 = rowfist.createCell(totalLine);// 创建一列
				cellFirst7.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst7.setCellValue("时间");
				cellFirst7.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst8 = rowfist.createCell(totalLine);// 创建一列
				cellFirst8.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst8.setCellValue("单位价格");
				cellFirst8.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst9 = rowfist.createCell(totalLine);// 创建一列
				cellFirst9.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst9.setCellValue("总价格");
				cellFirst9.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst10 = rowfist.createCell(totalLine);// 创建一列
				cellFirst10.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst10.setCellValue("种类");
				cellFirst10.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst12 = rowfist.createCell(totalLine);// 创建一列
				cellFirst12.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst12.setCellValue("品牌");
				cellFirst12.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst13 = rowfist.createCell(totalLine);// 创建一列
				cellFirst13.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst13.setCellValue("规格");
				cellFirst13.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst2 = rowfist.createCell(totalLine);// 创建一列
				cellFirst2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst2.setCellValue("材料名");
				cellFirst2.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst11 = rowfist.createCell(totalLine);// 创建一列
				cellFirst11.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst11.setCellValue("单位");
				cellFirst11.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				HSSFCell cellFirst6 = rowfist.createCell(totalLine);// 创建一列
				cellFirst6.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst6.setCellValue("总重量");
				cellFirst6.setCellStyle(setBorder);
				totalLine = totalLine + 1;

				// 总表的分表标题
				List<Object[]> listCust = supplierService.getExcelCustForDistribute(shopIdAll);

				if (listCust != null && listCust.size() > 0) {
					for (int i = 0; i < listCust.size(); i++) {
						sheet.setColumnWidth(totalLine, 3000);
						sheet.setColumnWidth(totalLine + 1, 5000);

						HSSFCell cellFirstfen = rowfist.createCell(totalLine);
						totalLine = totalLine + 1;
						cellFirstfen.setCellType(HSSFCell.CELL_TYPE_STRING);
						cellFirstfen.setCellValue(listCust.get(i)[1].toString());
						// 分店名称
						cellFirstfen.setCellStyle(setBorder);

						HSSFCell cellFirstfenMessage = rowfist.createCell(totalLine);
						totalLine = totalLine + 1;
						cellFirstfenMessage.setCellType(HSSFCell.CELL_TYPE_STRING);
						cellFirstfenMessage.setCellValue(listCust.get(i)[1].toString() + "留言");
						// 分店名称
						cellFirstfenMessage.setCellStyle(setBorder);
					}
				}

				// 根据日期取得 总表数据
				List<Object[]> listTotal = supplierService.getAllTotalByUserIdALl(orderDate, supId, shopIdAll);

				for (int i = 0; i < listTotal.size(); i++) {

					Object[] a = listTotal.get(i);
					HSSFRow row = sheet.createRow((int) i + 1);// 创建一行
					row.setHeight((short) 340);// 行高

					int totalDateLine = 0;

					HSSFCell cell1 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setCellValue(a[0].toString());
					cell1.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell3 = row.createCell(totalDateLine);// 创建一列 材料品级
					cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell3.setCellValue(a[1].toString());
					cell3.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell4 = row.createCell(totalDateLine);// 创建一列 供货商ID
					cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell4.setCellValue(a[2].toString());
					cell4.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell5 = row.createCell(totalDateLine);// 创建一列
																	// 供货商name
					cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell5.setCellValue(a[5].toString());
					cell5.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell7 = row.createCell(totalDateLine);// 创建一列 时间
					cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell7.setCellValue(orderDate);
					cell7.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell8 = row.createCell(totalDateLine);// 创建一列 每公斤价格
					cell8.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell8.setCellStyle(setBorder);
					/*
					 * if (a[10] == null) { cell8.setCellValue(""); } else {
					 * BigDecimal materValue = new
					 * BigDecimal(a[10].toString()).multiply( new
					 * BigDecimal(1000)).setScale(2, BigDecimal.ROUND_HALF_UP);
					 * cell8.setCellValue(materValue.toString()); }
					 */

					totalDateLine = totalDateLine + 1;

					HSSFCell cell9 = row.createCell(totalDateLine);// 创建一列 总价格
					cell9.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell9.setCellStyle(setBorder);
					cell9.setCellFormula("F" + (i + 2) + "*M" + (i + 2));

					totalDateLine = totalDateLine + 1;

					HSSFCell cell10 = row.createCell(totalDateLine);// 创建一列 种类
					cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell10.setCellValue(a[6].toString());
					cell10.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell12 = row.createCell(totalDateLine);// 创建一列 品牌
					cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell12.setCellValue(a[8].toString());
					cell12.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell13 = row.createCell(totalDateLine);// 创建一列 规格
					cell13.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell13.setCellValue(a[9].toString());
					cell13.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell2 = row.createCell(totalDateLine);// 创建一列 材料名
					cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell2.setCellValue(a[4].toString());
					cell2.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell11 = row.createCell(totalDateLine);// 创建一列 单位
					cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell11.setCellValue(a[7].toString());
					cell11.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell6 = row.createCell(totalDateLine);// 创建一列
					cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell6.setCellValue(a[3].toString());
					cell6.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					if (listCust != null && listCust.size() > 0) {
						for (int j = 0; j < listCust.size(); j++) {
							String custId = listCust.get(j)[0].toString();
							String custName = listCust.get(j)[1].toString();

							List<Object[]> listCustTotalDate = supplierService.getExcelCustTotalRealMaterByDateMaterIdMaterLevel(orderDate, supId,
									custId, a[0].toString(), a[1].toString());
							if (listCustTotalDate != null && listCustTotalDate.size() > 0) {
								HSSFCell cellCustTotalDate = row.createCell(totalDateLine);
								// 创建一列
								cellCustTotalDate.setCellType(HSSFCell.CELL_TYPE_STRING);
								Object[] ad = (Object[]) listCustTotalDate.get(0);
								cellCustTotalDate.setCellValue(ad[0].toString());
								cellCustTotalDate.setCellStyle(setBorder);
								totalDateLine = totalDateLine + 1;

								HSSFCell cellCustTotalDateMessage = row.createCell(totalDateLine);
								// 创建一列
								cellCustTotalDateMessage.setCellType(HSSFCell.CELL_TYPE_STRING);
								if (ad[1] != null) {
									cellCustTotalDateMessage.setCellValue(ad[1].toString());

								}
								cellCustTotalDateMessage.setCellStyle(setBorder);
								totalDateLine = totalDateLine + 1;
							} else {
								HSSFCell cellCustTotalDate = row.createCell(totalDateLine);// 创建一列
								cellCustTotalDate.setCellType(HSSFCell.CELL_TYPE_STRING);
								cellCustTotalDate.setCellValue(" ");
								cellCustTotalDate.setCellStyle(setBorder);
								totalDateLine = totalDateLine + 1;

								HSSFCell cellCustTotalDateMessage = row.createCell(totalDateLine);
								// 创建一列
								cellCustTotalDateMessage.setCellType(HSSFCell.CELL_TYPE_STRING);
								cellCustTotalDateMessage.setCellValue("");
								cellCustTotalDateMessage.setCellStyle(setBorder);
								totalDateLine = totalDateLine + 1;
							}
						}
					}

				}
				sheet.setForceFormulaRecalculation(true);
			}
			// fOut = response.getOutputStream();
			// workbook.write(fOut);

			InputStream excelStream = null;

			ByteArrayOutputStream out = new ByteArrayOutputStream();

			HSSFWorkbook hssWb = (HSSFWorkbook) workbook;

			hssWb.write(out);

			excelStream = new ByteArrayInputStream(out.toByteArray());

			out.close();

			String realPath = session1.getServletContext().getRealPath("/");

			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint";

			FileUtill.saveFileFromInputStream(excelStream, filePath, supId + "_" + "分货单" + "_" + orderDate + ".xls");

		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("importSupDistribute.do", supId);

		}

		json.put("success", true);
		return json.toString();
	}

	/**
	 * 下载分货表
	 */
	@RequestMapping(value = "/importSupDistributeDown.do", method = { RequestMethod.POST })
	@ResponseBody
	public String importSupDistributeDown(HttpServletRequest request, HttpServletResponse response, HttpSession session1, String orderDate, int supId)
			throws Exception {
		JSONObject json = new JSONObject();

		UserPo userInfo = (UserPo) session1.getAttribute("user");
		// 根据用户id得出supid
		if (supId == 0) {
			supId = supplierService.getSupIdByUserId(userInfo.getUserId());
		}

		boolean lockFlag = lockTransService.lockTrans("importSupDistribute.do", supId);
		if (!lockFlag) {
			json.put("msg", "locked");
			json.put("success", false);
			return json.toString();
		}
		OutputStream toClient = null;
		try {

			String filename = supId + "_" + "分货单" + "_" + orderDate + ".xls";
			// 以流的形式下载文件。
			String realPath = session1.getServletContext().getRealPath("/");
			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint"
					+ File.separator;
			InputStream fis = new BufferedInputStream(new FileInputStream(filePath + filename));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);

			// 清空response
			response.reset();
			// 设置response的Header
			// response.addHeader("Content-Disposition", "attachment;filename="
			// + new String(filename.getBytes()));

			response.setContentType("octets/stream");
			response.addHeader("Content-Type", "text/html; charset=utf-8");
			String downLoadName = new String(filename.getBytes("gbk"), "iso8859-1");
			response.addHeader("Content-Disposition", "attachment;filename=" + downLoadName);

			toClient = new BufferedOutputStream(response.getOutputStream());
			toClient.write(buffer);
			fis.close();
		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("importSupDistribute.do", supId);

			try {
				toClient.flush();
				toClient.close();
			} catch (IOException e) {
			}
		}
		json.put("msg", "msg001");
		json.put("success", false);
		return json.toString();
	}

	/**
	 * 生成分货表
	 * 
	 */
	@RequestMapping(value = "/importSupDistributeSummarySheet.do", method = { RequestMethod.POST })
	@ResponseBody
	public String importSupDistributeSummarySheet(HttpServletRequest request, HttpServletResponse response, HttpSession session1, String orderDate,
			int supId, String shopIdAll) throws Exception {

		JSONObject json = new JSONObject();
		HttpSession session = request.getSession();
		session.setAttribute("state", null);
		// 生成提示信息，
		String codedFileName = null;
		OutputStream fOut = null;
		UserPo userInfo = (UserPo) session1.getAttribute("user");

		/*
		 * // 增加对时间的校验 boolean flagTime = false; try { flagTime =
		 * timeControlService.timeControl(userInfo.getUserId(), orderDate); }
		 * catch (Exception e2) { e2.printStackTrace(); } if (!flagTime && supId
		 * == 0) { json.put("msg", "msg001");
		 * 
		 * json.put("success", false); return json.toString(); }
		 */

		try {

			// 根据用户id得出supid

			if (supId == 0) {
				supId = supplierService.getSupIdByUserId(userInfo.getUserId());
			}
			// 库存id
			int storeHouseId = supplierService.getSupStoreHouseIdByUserId(supId);

			boolean lockFlag = lockTransService.lockTrans("importSupDistributeSummarySheet.do", supId);
			if (!lockFlag) {
				json.put("msg", "locked");
				json.put("success", false);
				return json.toString();
			}
			// 进行转码，使其支持中文文件名
			// codedFileName = java.net.URLEncoder.encode("Quotation" + supId +
			// "_" + orderDate, "UTF-8");
			// response.addHeader("Content-Disposition",
			// "attachment;   filename=" + codedFileName + ".xls");
			// 产生工作簿对象
			HSSFWorkbook workbook = new HSSFWorkbook();

			// 产生工作表对象 所有材料的分货单
			HSSFSheet sheet = workbook.createSheet();
			// 打印格式
			sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3);// 页边距（上）
			sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3);// 页边距（下）
			sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.3);// 页边距（左）
			sheet.setMargin(HSSFSheet.RightMargin, (double) 0.3);// 页边距（右
			sheet.setHorizontallyCenter(true);// 打印水平居中

			// 设定自动适应列宽
			for (int i = 0; i < 20; i++) {
				sheet.autoSizeColumn(i);
			}
			// 边框
			HSSFCellStyle setBorder = workbook.createCellStyle();
			setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			setBorder.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
			setBorder.setWrapText(true);
			// sheet.setColumnHidden(0, true);// 隐藏列
			/*
			 * sheet.setColumnHidden(1, true);// 隐藏列 sheet.setColumnHidden(2,
			 * true);// 隐藏列 sheet.setColumnHidden(3, true);// 隐藏列
			 * sheet.setColumnHidden(4, true);// 隐藏列 sheet.setColumnHidden(5,
			 * true);// 隐藏列
			 * 
			 * sheet.setColumnHidden(6, true);// 隐藏列 sheet.setColumnHidden(8,
			 * true);// 隐藏列 sheet.setColumnHidden(12, true);// 隐藏列 总重量
			 */

			// sheet.setColumnHidden(1, true);// 隐藏列
			sheet.setColumnHidden(9, true);// 隐藏列

			sheet.setColumnWidth(0, 1000);// 宽度设置
			sheet.setColumnWidth(1, 2000);// 宽度设置
			sheet.setColumnWidth(2, 2500);// 宽度设置
			sheet.setColumnWidth(3, 3000);// 宽度设置
			sheet.setColumnWidth(4, 1000);// 宽度设置
			sheet.setColumnWidth(5, 6000);// 宽度设置
			sheet.setColumnWidth(6, 2000);// 宽度设置

			sheet.setColumnWidth(7, 2000);// 宽度设置
			sheet.setColumnWidth(8, 2000);// 宽度设置
			sheet.setColumnWidth(9, 2500);// 宽度设置
			sheet.setColumnWidth(10, 6000);// 宽度设置
			sheet.setColumnWidth(11, 6000);// 宽度设置
			sheet.setColumnWidth(12, 6000);// 宽度设置
			sheet.setColumnWidth(13, 6000);// 宽度设置

			// 准备下拉框的数据
			List<SupplierPo> supplierPoList = supplierService.getSupPoAll();
			List<String> changeList = new ArrayList<String>();
			for (SupplierPo supplierPo : supplierPoList) {
				changeList.add(supplierPo.getCompanyName());
			}
			int changeListsize = changeList.size();
			String[] listDropDown1 = (String[]) changeList.toArray(new String[changeListsize]);
			// 初始行设置
			HSSFRow rowfist = sheet.createRow((int) 0);// 创建一行
			rowfist.setHeight((short) 340);// 行高

			int totalLine = 0;

			HSSFCell cellFirst1 = rowfist.createCell(totalLine);// 创建一列
			cellFirst1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst1.setCellValue("种类");
			cellFirst1.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst2 = rowfist.createCell(totalLine);// 创建一列
			cellFirst2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst2.setCellValue("材料id");
			cellFirst2.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst3 = rowfist.createCell(totalLine);// 创建一列
			cellFirst3.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst3.setCellValue("材料名");
			cellFirst3.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst4 = rowfist.createCell(totalLine);// 创建一列
			cellFirst4.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst4.setCellValue("规格");
			cellFirst4.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst5 = rowfist.createCell(totalLine);// 创建一列
			cellFirst5.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst5.setCellValue("单位");
			cellFirst5.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst7 = rowfist.createCell(totalLine);// 创建一列
			cellFirst7.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst7.setCellValue("分货量");
			cellFirst7.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst8 = rowfist.createCell(totalLine);// 创建一列
			cellFirst8.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst8.setCellValue("总量");
			cellFirst8.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst9 = rowfist.createCell(totalLine);// 创建一列
			cellFirst9.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst9.setCellValue("库存量");
			cellFirst9.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst10 = rowfist.createCell(totalLine);// 创建一列
			cellFirst10.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst10.setCellValue("采购量");
			cellFirst10.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst11 = rowfist.createCell(totalLine);// 创建一列
			cellFirst11.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst11.setCellValue("材料lv");
			cellFirst11.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst12 = rowfist.createCell(totalLine);// 创建一列
			cellFirst12.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst12.setCellValue("供货商");
			cellFirst12.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			/*
			 * HSSFCell cellFirstT1 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT1.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT1.setCellValue("种类");
			 * cellFirstT1.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT2 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT2.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT2.setCellValue("材料id");
			 * cellFirstT2.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT3 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT3.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT3.setCellValue("材料名");
			 * cellFirstT3.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT4 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT4.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT4.setCellValue("规格");
			 * cellFirstT4.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT5 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT5.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT5.setCellValue("单位");
			 * cellFirstT5.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT7 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT7.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT7.setCellValue("分货量");
			 * cellFirstT7.setCellStyle(setBorder); totalLine = totalLine + 1;
			 * 
			 * HSSFCell cellFirstT8 = rowfist.createCell(totalLine);// 创建一列
			 * cellFirstT8.setCellType(HSSFCell.CELL_TYPE_STRING);
			 * cellFirstT8.setCellValue("总量");
			 * cellFirstT8.setCellStyle(setBorder); totalLine = totalLine + 1;
			 */

			// 根据日期取得 总表数据
			// List<Object[]> listTotal =
			// supplierService.getSupDistributeSummarySheet(orderDate, supId);
			List<SupplierDistributeMaterPo> supplierDistributeMaterPoList = supplierDistributeService.getSupplierDistributeMaterPoBySupId(supId);
			int rowflag = 1;
			for (int i = 0; i < supplierDistributeMaterPoList.size(); i++) {
				/*
				 * if (i % 2 != 0) { continue; }
				 */
				Float hieght = new Float(0.01F);// 计算行高的变量
				Float hieghtCompare = new Float(0.01F);// 计算行高的变量
				List<Object[]> listTotal = supplierService.getSupDistributeSummarySheetForMaterOrder(orderDate, supId, supplierDistributeMaterPoList
						.get(i).getMaterId(), storeHouseId, shopIdAll);
				if (listTotal != null && listTotal.size() == 1 && listTotal.get(0)[0] != null) {
					Object[] a = listTotal.get(0);
					HSSFRow row = sheet.createRow((int) rowflag);// 创建一行
					row.setHeight((short) 340);// 行高

					int totalDateLine = 0;

					HSSFCell cell1 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setCellValue(a[0].toString());
					cell1.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[0].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell2 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell2.setCellValue(a[1].toString());
					cell2.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell3 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell3.setCellValue(a[2].toString());
					cell3.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[2].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell4 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell4.setCellValue(a[3].toString());
					cell4.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;
					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[3].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell5 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell5.setCellValue(a[4].toString());
					cell5.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell6 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
					if (a[5] != null) {
						cell6.setCellValue("@" + a[5].toString() + "@");
						hieghtCompare = getExcelCellAutoHeight(a[5].toString(), 12f);
						if (hieght.compareTo(hieghtCompare) < 0) {
							hieght = hieghtCompare;
						}
					} else {
						cell6.setCellValue("商户没有摊位号请设置！");
					}
					cell6.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;
					// 设置高度

					HSSFCell cell7 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell7.setCellValue(a[6].toString());
					cell7.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell8 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell8.setCellType(HSSFCell.CELL_TYPE_STRING);
					BigDecimal materStore = new BigDecimal(0);
					if (a[7] != null) {
						cell8.setCellValue(a[7].toString());
						materStore = new BigDecimal(a[7].toString());
					} else {
						cell8.setCellValue(0);
					}
					cell8.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell9 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell9.setCellType(HSSFCell.CELL_TYPE_STRING);
					BigDecimal materAll = new BigDecimal(a[6].toString());
					BigDecimal materNeed = new BigDecimal(0);
					if (materAll.compareTo(materStore) > 0) {
						materNeed = materAll.subtract(materStore);
					}
					cell9.setCellValue(materNeed.toString());
					cell9.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell10 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell10.setCellValue(a[8].toString());
					cell10.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 供货商下拉框创建
					sheet = ExcelDropDownUtil.setHSSFValidation(sheet, listDropDown1, rowflag, rowflag, totalDateLine, totalDateLine);// 第一列的前501行都设置为选择列表形式.
					// 第二列的前501行都设置提示.
					// sheet = ExcelDropDownUtil.setHSSFPrompt(sheet,
					// "promt Title", "prompt Content", 0, 500, 1, 1);
					HSSFCell cell11 = row.createCell(totalDateLine);// 创建供货商下拉框
					cell11.setCellType(HSSFCell.CELL_TYPE_STRING);
					// //查询物品有没有被绑定 供货商
					DistributorSupplierMaterVo c = new DistributorSupplierMaterVo();
					c.setMaterId(Integer.valueOf(a[1].toString()));
					c.setDistributorId(supId);
					List<DistributorSupplierMaterPo> listdissupmater = supplierDao.getDistributorSupplierMaterPo(c);
					if (listdissupmater == null || listdissupmater.size() == 0) {
						cell11.setCellValue(supplierDao.getSupPoBySupId(String.valueOf(supId)).getCompanyName());
						cell11.setCellStyle(setBorder);
					} else {
						cell11.setCellValue(supplierDao.getSupPoBySupId(String.valueOf(listdissupmater.get(0).getSupId())).getCompanyName());
						cell11.setCellStyle(setBorder);
					}
					totalDateLine = totalDateLine + 1;

					HSSFCell cell12 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 查询留言
					List<Object> listLeaveMessage = supplierService.getSupDistributeSummarySheetLeaveMessageForMaterOrder(orderDate, supId,
							Integer.valueOf(a[1].toString()), shopIdAll);
					if (listLeaveMessage != null && listLeaveMessage.size() > 0&&listLeaveMessage.get(0)!=null &&!"null".equals(listLeaveMessage.get(0)) ) {
						cell12.setCellValue(listLeaveMessage.get(0).toString());
					} else {
						cell12.setCellValue("");
					}

					cell12.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 根据字符串的长度设置高度
					row.setHeightInPoints(hieght);
					// 为下一行做准备
					rowflag = 1 + rowflag;

				}
				/*
				 * // 第二列 if ((i + 1) < listTotal.size()) { Object[] b =
				 * listTotal.get(i + 1);
				 * 
				 * HSSFCell cellT1 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT1.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT1.setCellValue(b[0].toString());
				 * cellT1.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1;
				 * 
				 * // 设置高度 hieghtCompare =
				 * getExcelCellAutoHeight(b[0].toString(), 6f); if
				 * (hieght.compareTo(hieghtCompare) < 0) { hieght =
				 * hieghtCompare; }
				 * 
				 * HSSFCell cellT2 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT2.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT2.setCellValue(b[1].toString());
				 * cellT2.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1;
				 * 
				 * HSSFCell cellT3 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT3.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT3.setCellValue(b[2].toString());
				 * cellT3.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1;
				 * 
				 * // 设置高度 hieghtCompare =
				 * getExcelCellAutoHeight(b[2].toString(), 6f); if
				 * (hieght.compareTo(hieghtCompare) < 0) { hieght =
				 * hieghtCompare; }
				 * 
				 * HSSFCell cellT4 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT4.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT4.setCellValue(b[3].toString());
				 * cellT4.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1; // 设置高度 hieghtCompare =
				 * getExcelCellAutoHeight(b[3].toString(), 6f); if
				 * (hieght.compareTo(hieghtCompare) < 0) { hieght =
				 * hieghtCompare; }
				 * 
				 * HSSFCell cellT5 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT5.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT5.setCellValue(b[4].toString());
				 * cellT5.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1;
				 * 
				 * HSSFCell cellT6 = row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT6.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT6.setCellValue(b[5].toString());
				 * cellT6.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1; // 设置高度 hieghtCompare =
				 * getExcelCellAutoHeight(b[5].toString(), 12f); if
				 * (hieght.compareTo(hieghtCompare) < 0) { hieght =
				 * hieghtCompare; } HSSFCell cellT7 =
				 * row.createCell(totalDateLine);// 创建一列 材料Id
				 * cellT7.setCellType(HSSFCell.CELL_TYPE_STRING);
				 * cellT7.setCellValue(b[6].toString());
				 * cellT7.setCellStyle(setBorder); totalDateLine = totalDateLine
				 * + 1; }
				 */

			}

			workbook.setSheetName(0, "固定表格");

			sheet.setForceFormulaRecalculation(true);

			// 不固定的品类

			// 取得日期
			List<Object[]> listTotal = supplierService.getSupDistributeSummarySheetForGroupbyMaterType(orderDate, supId);

			for (int i = 0; i < listTotal.size(); i++) {

				List<Object[]> listDate = supplierService.getSupDistributeSummarySheet(orderDate, supId, listTotal.get(i)[0].toString(),
						storeHouseId, shopIdAll);
				HSSFSheet sheetsub = workbook.createSheet();
				workbook.setSheetName(i + 1, listTotal.get(i)[1].toString());

				// sheetsub.setColumnHidden(1, true);// 隐藏列
				sheetsub.setColumnHidden(9, true);// 隐藏列

				sheetsub.setColumnWidth(0, 1000);// 宽度设置
				sheetsub.setColumnWidth(1, 2000);// 宽度设置
				sheetsub.setColumnWidth(2, 2500);// 宽度设置
				sheetsub.setColumnWidth(3, 3000);// 宽度设置
				sheetsub.setColumnWidth(4, 1000);// 宽度设置
				sheetsub.setColumnWidth(5, 6000);// 宽度设置
				sheetsub.setColumnWidth(6, 2000);// 宽度设置

				sheetsub.setColumnWidth(7, 2000);// 宽度设置
				sheetsub.setColumnWidth(8, 2000);// 宽度设置
				sheetsub.setColumnWidth(9, 2500);// 宽度设置
				sheetsub.setColumnWidth(10, 6000);// 宽度设置
				sheetsub.setColumnWidth(11, 6000);// 宽度设置
				sheetsub.setColumnWidth(12, 6000);// 宽度设置
				sheetsub.setColumnWidth(13, 6000);// 宽度设置

				// 初始行设置
				HSSFRow rowfistSub = sheetsub.createRow((int) 0);// 创建一行
				rowfistSub.setHeight((short) 340);// 行高

				int totalLineSub = 0;

				HSSFCell cellFirst1Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst1Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst1Sub.setCellValue("种类");
				cellFirst1Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst2Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst2Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst2Sub.setCellValue("材料id");
				cellFirst2Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst3Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst3Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst3Sub.setCellValue("材料名");
				cellFirst3Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst4Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst4Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst4Sub.setCellValue("规格");
				cellFirst4Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst5Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst5Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst5Sub.setCellValue("单位");
				cellFirst5Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst7Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst7Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst7Sub.setCellValue("分货量");
				cellFirst7Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst8Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst8Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst8Sub.setCellValue("总量");
				cellFirst8Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst9Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst9Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst9Sub.setCellValue("库存量");
				cellFirst9Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst10Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst10Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst10Sub.setCellValue("采购量");
				cellFirst10Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst11Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst11Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst11Sub.setCellValue("材料lv");
				cellFirst11Sub.setCellStyle(setBorder);
				totalLineSub = totalLineSub + 1;

				HSSFCell cellFirst12Sub = rowfistSub.createCell(totalLineSub);// 创建一列
				cellFirst12Sub.setCellType(HSSFCell.CELL_TYPE_STRING);
				cellFirst12Sub.setCellValue("供货商");
				cellFirst12Sub.setCellStyle(setBorder);
				totalLine = totalLine + 1;
				for (int j = 0; j < listDate.size(); j++) {
					Float hieght = new Float(0.01F);// 计算行高的变量
					Float hieghtCompare = new Float(0.01F);// 计算行高的变量
					Object[] a = listDate.get(j);
					HSSFRow row = sheetsub.createRow((int) j + 1);// 创建一行
					row.setHeight((short) 340);// 行高

					int totalDateLine = 0;

					HSSFCell cell1 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell1.setCellValue(a[0].toString());
					cell1.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[0].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell2 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell2.setCellValue(a[1].toString());
					cell2.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell3 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell3.setCellValue(a[2].toString());
					cell3.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[2].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell4 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell4.setCellValue(a[3].toString());
					cell4.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;
					// 设置高度
					hieghtCompare = getExcelCellAutoHeight(a[3].toString(), 6f);
					if (hieght.compareTo(hieghtCompare) < 0) {
						hieght = hieghtCompare;
					}

					HSSFCell cell5 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell5.setCellValue(a[4].toString());
					cell5.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell6 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
					if (a[5] != null) {
						cell6.setCellValue("@" + a[5].toString() + "@");
						hieghtCompare = getExcelCellAutoHeight(a[5].toString(), 12f);
						if (hieght.compareTo(hieghtCompare) < 0) {
							hieght = hieghtCompare;
						}
					} else {
						cell6.setCellValue("商户没有摊位号请设置！");
					}
					cell6.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;
					// 设置高度

					HSSFCell cell7 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell7.setCellValue(a[6].toString());
					cell7.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell8 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell8.setCellType(HSSFCell.CELL_TYPE_STRING);
					BigDecimal materStore = new BigDecimal(0);
					if (a[7] != null) {
						materStore = new BigDecimal(a[7].toString());
						cell8.setCellValue(a[7].toString());
					} else {
						cell8.setCellValue(0);
					}
					cell8.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell9 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell9.setCellType(HSSFCell.CELL_TYPE_STRING);
					BigDecimal materAll = new BigDecimal(a[6].toString());
					BigDecimal materNeed = new BigDecimal(0);
					if (materAll.compareTo(materStore) > 0) {
						materNeed = materAll.subtract(materStore);
					}
					cell9.setCellValue(materNeed.toString());
					cell9.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					HSSFCell cell10 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell10.setCellType(HSSFCell.CELL_TYPE_STRING);
					cell10.setCellValue(a[8].toString());
					cell10.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;

					// 供货商下拉框创建

					DistributorSupplierMaterVo c = new DistributorSupplierMaterVo();
					c.setMaterId(Integer.valueOf(a[1].toString()));
					c.setDistributorId(supId);
					List<DistributorSupplierMaterPo> listdissupmater = supplierDao.getDistributorSupplierMaterPo(c);

					sheetsub = ExcelDropDownUtil.setHSSFValidation(sheetsub, listDropDown1, j, j, totalDateLine, totalDateLine);// 第一列的前501行都设置为选择列表形式.
					// 第二列的前501行都设置提示.
					// sheet = ExcelDropDownUtil.setHSSFPrompt(sheet,
					// "promt Title", "prompt Content", 0, 500, 1, 1);
					HSSFCell cell11 = row.createCell(totalDateLine);// 创建供货商下拉框
					if (listdissupmater == null || listdissupmater.size() == 0) {
						cell11.setCellValue(supplierDao.getSupPoBySupId(String.valueOf(supId)).getCompanyName());
						cell11.setCellStyle(setBorder);
					} else {
						cell11.setCellValue(supplierDao.getSupPoBySupId(String.valueOf(listdissupmater.get(0).getSupId())).getCompanyName());
						cell11.setCellStyle(setBorder);
					}
					totalDateLine = totalDateLine + 1;
					
					HSSFCell cell12 = row.createCell(totalDateLine);// 创建一列 材料Id
					cell12.setCellType(HSSFCell.CELL_TYPE_STRING);
					// 查询留言
					List<Object> listLeaveMessage = supplierService.getSupDistributeSummarySheetLeaveMessageForMaterOrder(orderDate, supId,
							Integer.valueOf(a[1].toString()), shopIdAll);
					if (listLeaveMessage != null && listLeaveMessage.size() > 0&&listLeaveMessage.get(0)!=null &&!"null".equals(listLeaveMessage.get(0)) ) {
						cell12.setCellValue(listLeaveMessage.get(0).toString());
					} else {
						cell12.setCellValue("");
					}

					cell12.setCellStyle(setBorder);
					totalDateLine = totalDateLine + 1;
					
					// 根据字符串的长度设置高度
					row.setHeightInPoints(hieght);
					// 为下一行做准备

					// 根据字符串的长度设置高度
					row.setHeightInPoints(hieght);
					// sheetsub.protectSheet("123");
				}
			}
			InputStream excelStream = null;

			ByteArrayOutputStream out = new ByteArrayOutputStream();

			HSSFWorkbook hssWb = (HSSFWorkbook) workbook;

			hssWb.write(out);

			excelStream = new ByteArrayInputStream(out.toByteArray());

			out.close();

			String realPath = session1.getServletContext().getRealPath("/");

			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint";

			FileUtill.saveFileFromInputStream(excelStream, filePath, supId + "_" + "分货汇总单" + "_" + orderDate + ".xls");

		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("importSupDistributeSummarySheet.do", supId);

		}

		json.put("success", true);
		return json.toString();
	}

	// 自动计算行高(根据字符串长度)
	public static float getExcelCellAutoHeight(String str, float fontCountInline) {
		float defaultRowHeight = 15.00f;// 每一行的高度指定
		float defaultCount = 0.00f;
		for (int i = 0; i < str.length(); i++) {
			float ff = getregex(str.substring(i, i + 1));
			defaultCount = defaultCount + ff;
		}
		return ((int) (defaultCount / fontCountInline) + 1) * defaultRowHeight;// 计算
	}

	// 自动计算行高(根据字符串换行符\n)
	public static float getExcelCellAutoHeightBySeparator(String str, float fontCountInline) {
		float defaultRowHeight = 15.00f;// 每一行的高度指定
		System.out.println(str);
		String[] b = str.split("\\n");
		return b.length * defaultRowHeight;// 计算
	}

	public static float getregex(String charStr) {

		if (charStr == " ") {
			return 0.5f;
		}
		// 判断是否为字母或字符
		if (Pattern.compile("^[A-Za-z0-9]+$").matcher(charStr).matches()) {
			return 0.5f;
		}
		// 判断是否为全角

		if (Pattern.compile("[\u4e00-\u9fa5]+$").matcher(charStr).matches()) {
			return 1.00f;
		}
		// 全角符号 及中文
		if (Pattern.compile("[^x00-xff]").matcher(charStr).matches()) {
			return 1.00f;
		}
		return 0.5f;

	}

	/**
	 * 下载分货表
	 */
	@RequestMapping(value = "/importSupDistributeSummarySheetDown.do", method = { RequestMethod.POST })
	@ResponseBody
	public String importSupDistributeSummarySheetDown(HttpServletRequest request, HttpServletResponse response, HttpSession session1,
			String orderDate, int supId) throws Exception {
		JSONObject json = new JSONObject();

		UserPo userInfo = (UserPo) session1.getAttribute("user");
		// 根据用户id得出supid
		if (supId == 0) {
			supId = supplierService.getSupIdByUserId(userInfo.getUserId());
		}

		boolean lockFlag = lockTransService.lockTrans("importSupDistributeSummarySheetDown.do", supId);
		if (!lockFlag) {
			json.put("msg", "locked");
			json.put("success", false);
			return json.toString();
		}
		OutputStream toClient = null;
		try {

			String filename = supId + "_" + "分货汇总单" + "_" + orderDate + ".xls";
			// 以流的形式下载文件。
			String realPath = session1.getServletContext().getRealPath("/");
			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint"
					+ File.separator;
			InputStream fis = new BufferedInputStream(new FileInputStream(filePath + filename));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);

			// 清空response
			response.reset();
			// 设置response的Header
			// response.addHeader("Content-Disposition", "attachment;filename="
			// + new String(filename.getBytes()));

			response.setContentType("octets/stream");
			response.addHeader("Content-Type", "text/html; charset=utf-8");
			String downLoadName = new String(filename.getBytes("gbk"), "iso8859-1");
			response.addHeader("Content-Disposition", "attachment;filename=" + downLoadName);

			toClient = new BufferedOutputStream(response.getOutputStream());
			toClient.write(buffer);
			fis.close();
		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("importSupDistributeSummarySheetDown.do", supId);
			try {
				toClient.flush();
				toClient.close();
			} catch (IOException e) {
			}
		}
		json.put("msg", "msg001");
		json.put("success", false);
		return json.toString();
	}

	@RequestMapping(value = "/findMaterByDateAndMaterType.do", method = { RequestMethod.POST })
	@ResponseBody
	public String findMaterByDateAndMaterType(@RequestParam(required = false) String page, @RequestParam(required = false) String rows,
			SupplierDistributeMateVo supplierDistributeMateVo, HttpSession session) throws GenericException {
		JSONObject json = new JSONObject();
		page = "1";
		rows = "999";

		if (supplierDistributeMateVo.getInitFlag() == 0) {
			json.put("success", false);
			return json.toString();
		}
		PageController pageController = new PageController(Integer.parseInt(page), Integer.parseInt(rows));

		// 设置查询条件 ----下
		UserPo userInfo = (UserPo) session.getAttribute("user");
		int supId = supplierService.getSupIdByUserId(userInfo.getUserId());

		supplierDistributeMateVo.setSupId(supId);
		// 设置查询条件 ----上
		this.pageList = supplierDistributeService.findDistributeMater(pageController, supplierDistributeMateVo);

		List<SupplierDistributeMateVo> supplierDistributeMateVoList = new ArrayList<SupplierDistributeMateVo>();
		List items = pageList.getItems();
		if (items != null) {
			for (int i = 0; i < items.size(); i++) {
				Object[] item = (Object[]) items.get(i);
				SupplierDistributeMateVo f = new SupplierDistributeMateVo();
				f.setMaterId((Integer) item[0]);

				supplierDistributeMateVoList.add(f);
			}
		}
		json.put("success", true);
		json.put("total", pageList.getTotalCount());
		json.put("rows", supplierDistributeMateVoList);
		log.info("----" + json.toString());
		return json.toString();
	}

	// 快速检索材料json
	@RequestMapping(value = "/checkSupplierDistributeForGather.do", method = { RequestMethod.POST })
	@ResponseBody
	public String checkSupplierDistributeForGather(MaterVo tMaterVo, HttpServletRequest request, HttpServletResponse response, HttpSession session) {
		JSONObject json = new JSONObject();
		json.put("success", true);
		response.setContentType("text/json;charset=UTF-8");
		String userLevel = "";
		try {

			UserPo userInfo = (UserPo) session.getAttribute("user");
			tMaterVo.setSupId(supplierService.getSupIdByUserId(userInfo.getUserId()));
			userLevel = supplierDistributeService.checkSupplierDistributeForGather(tMaterVo);
			json.put("stringJson", userLevel);
		} catch (GenericException e) {
			e.printStackTrace();
		}
		return json.toString();

	}

	@RequestMapping(value = "/findMaterForSupplierDistribute.do", method = { RequestMethod.POST })
	@ResponseBody
	public String findMaterForSupplierDistribute(@RequestParam(required = false) String page, @RequestParam(required = false) String rows,
			SupplierDistributeMateVo supplierDistributeMateVo, HttpSession session) throws GenericException {
		JSONObject json = new JSONObject();
		page = "1";
		rows = "999";
		PageController pageController = new PageController(Integer.parseInt(page), Integer.parseInt(rows));

		// 设置查询条件 ----下
		UserPo userInfo = (UserPo) session.getAttribute("user");
		int supId = supplierService.getSupIdByUserId(userInfo.getUserId());

		supplierDistributeMateVo.setSupId(supId);
		// 设置查询条件 ----上
		this.pageList = supplierDistributeService.findDistributeMaterForGather(pageController, supplierDistributeMateVo);

		List<SupplierDistributeMateVo> supplierDistributeMateVoList = new ArrayList<SupplierDistributeMateVo>();
		List items = pageList.getItems();
		if (items != null) {
			for (int i = 0; i < items.size(); i++) {
				Object[] item = (Object[]) items.get(i);
				SupplierDistributeMateVo f = new SupplierDistributeMateVo();
				f.setMaterId((Integer) item[0]);
				f.setMaterType((Integer) item[1]);
				f.setMaterName((String) item[2]);
				f.setUnit((String) item[3]);
				f.setBrands((String) item[4]);
				f.setSpecifications((String) item[5]);
				f.setOrderMater((Integer) item[6]);
				supplierDistributeMateVoList.add(f);
			}
		}
		json.put("success", true);
		json.put("total", pageList.getTotalCount());
		json.put("rows", supplierDistributeMateVoList);
		log.info("----" + json.toString());
		return json.toString();
	}

	@RequestMapping(value = "/saveSupplierDistributeMaterForGather.do", method = { RequestMethod.POST })
	@ResponseBody
	public String saveSupplierDistributeMaterForGather(String materdategridList, HttpSession session) {
		JSONObject json = new JSONObject();
		UserPo userInfo = (UserPo) session.getAttribute("user");

		try {
			int supId = supplierService.getSupIdByUserId(userInfo.getUserId());

			supplierDistributeService.addSupplierDistributeMaterPo(materdategridList, supId);
			json.put("success", true);
		} catch (Exception e) {
			e.printStackTrace();
			json.put("success", false);
		}
		return json.toString();
	}

	@RequestMapping(value = "/deleteSupplierDistributeMater.do", method = { RequestMethod.POST })
	@ResponseBody
	public String deleteSupplierDistributeMater(int materId, HttpSession session) {
		JSONObject json = new JSONObject();
		UserPo userInfo = (UserPo) session.getAttribute("user");

		try {
			int supId = supplierService.getSupIdByUserId(userInfo.getUserId());

			supplierDistributeService.deleteSupplierDistributeMaterPo(materId, supId);
			json.put("success", true);
		} catch (Exception e) {
			e.printStackTrace();
			json.put("success", false);
		}
		return json.toString();
	}

	@RequestMapping(value = "/updateSupplierDistributeMater.do", method = { RequestMethod.POST })
	@ResponseBody
	public String updateSupplierDistributeMater(String materdategridList, HttpSession session) {
		JSONObject json = new JSONObject();
		UserPo userInfo = (UserPo) session.getAttribute("user");

		try {
			int supId = supplierService.getSupIdByUserId(userInfo.getUserId());

			supplierDistributeService.updateSupplierDistributeMaterPo(materdategridList, supId);
			json.put("success", true);
		} catch (Exception e) {
			e.printStackTrace();
			json.put("success", false);
		}
		return json.toString();
	}

	/**
	 * 生成留言汇总表
	 * 
	 */
	@RequestMapping(value = "/exportLeaveMessage.do", method = { RequestMethod.POST })
	@ResponseBody
	public String exportLeaveMessage(HttpServletRequest request, HttpServletResponse response, HttpSession session1, String orderDate, int supId)
			throws Exception {

		JSONObject json = new JSONObject();
		HttpSession session = request.getSession();
		session.setAttribute("state", null);
		// 生成提示信息，
		String codedFileName = null;
		OutputStream fOut = null;
		UserPo userInfo = (UserPo) session1.getAttribute("user");

		/*
		 * // 增加对时间的校验 boolean flagTime = false; try { flagTime =
		 * timeControlService.timeControl(userInfo.getUserId(), orderDate); }
		 * catch (Exception e2) { e2.printStackTrace(); } if (!flagTime && supId
		 * == 0) { json.put("msg", "msg001");
		 * 
		 * json.put("success", false); return json.toString(); }
		 */

		try {

			// 根据用户id得出supid

			if (supId == 0) {
				supId = supplierService.getSupIdByUserId(userInfo.getUserId());
			}

			boolean lockFlag = lockTransService.lockTrans("exportLeaveMessage.do", supId);
			if (!lockFlag) {
				json.put("msg", "locked");
				json.put("success", false);
				return json.toString();
			}
			// 进行转码，使其支持中文文件名
			// codedFileName = java.net.URLEncoder.encode("Quotation" + supId +
			// "_" + orderDate, "UTF-8");
			// response.addHeader("Content-Disposition",
			// "attachment;   filename=" + codedFileName + ".xls");
			// 产生工作簿对象
			HSSFWorkbook workbook = new HSSFWorkbook();

			// 产生工作表对象 所有材料的分货单
			HSSFSheet sheet = workbook.createSheet();
			// 打印格式
			sheet.setMargin(HSSFSheet.TopMargin, (double) 0.3);// 页边距（上）
			sheet.setMargin(HSSFSheet.BottomMargin, (double) 0.3);// 页边距（下）
			sheet.setMargin(HSSFSheet.LeftMargin, (double) 0.3);// 页边距（左）
			sheet.setMargin(HSSFSheet.RightMargin, (double) 0.3);// 页边距（右
			sheet.setHorizontallyCenter(true);// 打印水平居中

			// 设定自动适应列宽
			for (int i = 0; i < 20; i++) {
				sheet.autoSizeColumn(i);
			}
			// 边框
			HSSFCellStyle setBorder = workbook.createCellStyle();
			setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
			setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
			setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
			setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
			setBorder.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
			setBorder.setWrapText(true);
			// sheet.setColumnHidden(0, true);// 隐藏列
			/*
			 * sheet.setColumnHidden(1, true);// 隐藏列 sheet.setColumnHidden(2,
			 * true);// 隐藏列 sheet.setColumnHidden(3, true);// 隐藏列
			 * sheet.setColumnHidden(4, true);// 隐藏列 sheet.setColumnHidden(5,
			 * true);// 隐藏列
			 * 
			 * sheet.setColumnHidden(6, true);// 隐藏列 sheet.setColumnHidden(8,
			 * true);// 隐藏列 sheet.setColumnHidden(12, true);// 隐藏列 总重量
			 */

			/*
			 * sheet.setColumnHidden(1, true);// 隐藏列 sheet.setColumnHidden(8,
			 * true);// 隐藏列
			 */
			sheet.setColumnWidth(0, 6000);// 宽度设置

			sheet.setColumnWidth(8, 6000);// 宽度设置

			int sheetIndex = 0;
			// 初始行设置
			HSSFRow rowfist = sheet.createRow((int) 0);// 创建一行
			rowfist.setHeight((short) 340);// 行高

			int totalLine = 0;

			HSSFCell cellFirst11 = rowfist.createCell(totalLine);// 创建一列
			cellFirst11.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst11.setCellValue("店名");
			cellFirst11.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst1 = rowfist.createCell(totalLine);// 创建一列
			cellFirst1.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst1.setCellValue("种类");
			cellFirst1.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst2 = rowfist.createCell(totalLine);// 创建一列
			cellFirst2.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst2.setCellValue("材料id");
			cellFirst2.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst3 = rowfist.createCell(totalLine);// 创建一列
			cellFirst3.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst3.setCellValue("材料名");
			cellFirst3.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst4 = rowfist.createCell(totalLine);// 创建一列
			cellFirst4.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst4.setCellValue("规格");
			cellFirst4.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst55 = rowfist.createCell(totalLine);// 创建一列
			cellFirst55.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst55.setCellValue("需求数量");
			cellFirst55.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst5 = rowfist.createCell(totalLine);// 创建一列
			cellFirst5.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst5.setCellValue("单位");
			cellFirst5.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst7 = rowfist.createCell(totalLine);// 创建一列
			cellFirst7.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst7.setCellValue("需求量");
			cellFirst7.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			HSSFCell cellFirst8 = rowfist.createCell(totalLine);// 创建一列
			cellFirst8.setCellType(HSSFCell.CELL_TYPE_STRING);
			cellFirst8.setCellValue("留言");
			cellFirst8.setCellStyle(setBorder);
			totalLine = totalLine + 1;

			// 根据日期取得 总表数据
			// List<Object[]> listTotal =
			// supplierService.getSupDistributeSummarySheet(orderDate, supId);
			List<Object[]> supplierDistributeMaterPoList = supplierService.getSupDistributLeaveMessage(orderDate, supId);
			for (int i = 0; i < supplierDistributeMaterPoList.size(); i++) {
				Float hieght = new Float(0.01F);// 计算行高的变量
				Float hieghtCompare = new Float(0.01F);// 计算行高的变量

				Object[] a = supplierDistributeMaterPoList.get(i);
				HSSFRow row = sheet.createRow((int) i + 1);// 创建一行
				row.setHeight((short) 340);// 行高

				int totalDateLine = 0;

				HSSFCell cell11 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell11.setCellType(HSSFCell.CELL_TYPE_STRING);

				if (a[6] != null && !"".equals(a[6])) {
					cell11.setCellValue(a[6].toString() + "号-" + a[9].toString());
				} else {
					cell11.setCellValue(a[9].toString());
				}
				cell11.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				HSSFCell cell1 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell1.setCellValue(a[0].toString());
				cell1.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				// 设置高度
				hieghtCompare = getExcelCellAutoHeight(a[0].toString(), 6f);
				if (hieght.compareTo(hieghtCompare) < 0) {
					hieght = hieghtCompare;
				}

				HSSFCell cell2 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell2.setCellValue(a[1].toString());
				cell2.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				HSSFCell cell3 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell3.setCellValue(a[2].toString());
				cell3.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				// 设置高度
				hieghtCompare = getExcelCellAutoHeight(a[2].toString(), 6f);
				if (hieght.compareTo(hieghtCompare) < 0) {
					hieght = hieghtCompare;
				}

				HSSFCell cell4 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell4.setCellValue(a[3].toString());
				cell4.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;
				// 设置高度
				hieghtCompare = getExcelCellAutoHeight(a[3].toString(), 6f);
				if (hieght.compareTo(hieghtCompare) < 0) {
					hieght = hieghtCompare;
				}

				HSSFCell cell55 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell55.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell55.setCellValue(a[4].toString());
				cell55.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				HSSFCell cell5 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell5.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell5.setCellValue(a[5].toString());
				cell5.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				HSSFCell cell6 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell6.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell6.setCellValue(a[4].toString());
				cell6.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;
				// 设置高度
				hieghtCompare = getExcelCellAutoHeight(a[5].toString(), 12f);
				if (hieght.compareTo(hieghtCompare) < 0) {
					hieght = hieghtCompare;
				}
				HSSFCell cell7 = row.createCell(totalDateLine);// 创建一列 材料Id
				cell7.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell7.setCellValue(a[7].toString());
				cell7.setCellStyle(setBorder);
				totalDateLine = totalDateLine + 1;

				// 根据字符串的长度设置高度
				sheet.getRow(sheet.getLastRowNum()).setHeightInPoints(hieght);
			}

			workbook.setSheetName(0, "留言汇总");

			sheet.setForceFormulaRecalculation(true);

			InputStream excelStream = null;

			ByteArrayOutputStream out = new ByteArrayOutputStream();

			HSSFWorkbook hssWb = (HSSFWorkbook) workbook;

			hssWb.write(out);

			excelStream = new ByteArrayInputStream(out.toByteArray());

			out.close();

			String realPath = session1.getServletContext().getRealPath("/");

			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint";

			FileUtill.saveFileFromInputStream(excelStream, filePath, supId + "_" + "留言汇总单" + "_" + orderDate + ".xls");

		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("exportLeaveMessage.do", supId);

		}

		json.put("success", true);
		return json.toString();
	}

	/**
	 * 下载分货表
	 */
	@RequestMapping(value = "/exportLeaveMessageDown.do", method = { RequestMethod.POST })
	@ResponseBody
	public String exportLeaveMessageDown(HttpServletRequest request, HttpServletResponse response, HttpSession session1, String orderDate, int supId)
			throws Exception {
		JSONObject json = new JSONObject();

		UserPo userInfo = (UserPo) session1.getAttribute("user");
		// 根据用户id得出supid
		if (supId == 0) {
			supId = supplierService.getSupIdByUserId(userInfo.getUserId());
		}

		boolean lockFlag = lockTransService.lockTrans("exportLeaveMessage.do", supId);
		if (!lockFlag) {
			json.put("msg", "locked");
			json.put("success", false);
			return json.toString();
		}
		OutputStream toClient = null;
		try {

			String filename = supId + "_" + "留言汇总单" + "_" + orderDate + ".xls";
			// 以流的形式下载文件。
			String realPath = session1.getServletContext().getRealPath("/");
			String filePath = realPath + File.separator + "jsp" + File.separator + "templet" + File.separator + "excelSupperLookPrint"
					+ File.separator;
			InputStream fis = new BufferedInputStream(new FileInputStream(filePath + filename));
			byte[] buffer = new byte[fis.available()];
			fis.read(buffer);

			// 清空response
			response.reset();
			// 设置response的Header
			// response.addHeader("Content-Disposition", "attachment;filename="
			// + new String(filename.getBytes()));

			response.setContentType("octets/stream");
			response.addHeader("Content-Type", "text/html; charset=utf-8");
			String downLoadName = new String(filename.getBytes("gbk"), "iso8859-1");
			response.addHeader("Content-Disposition", "attachment;filename=" + downLoadName);

			toClient = new BufferedOutputStream(response.getOutputStream());
			toClient.write(buffer);
			fis.close();
		} catch (Exception e) {
		} finally {
			lockTransService.unlockTrans("exportLeaveMessage.do", supId);
			try {
				toClient.flush();
				toClient.close();
			} catch (IOException e) {
			}
		}
		json.put("msg", "msg001");
		json.put("success", false);
		return json.toString();
	}

}
